
<html><HEAD>
<LINK REL=STYLESHEET HREF="default.css" TYPE="text/css">
<TITLE>
Using Oracle stored procedures as a data source</TITLE>
</HEAD>
<BODY>

<!-- Header -->
<p class="ancestor" align="right"><A HREF="connpbp68.htm">Previous</A>&nbsp;&nbsp;<A HREF="connpbp70.htm" >Next</A>
<!-- End Header -->
<A NAME="CCJDICBE"></A><h1>Using Oracle stored procedures as a data source</h1>
<A NAME="TI1447"></A><p>This section describes how you can use Oracle stored procedures.</p>
<A NAME="TI1448"></A><h2>What is an Oracle stored procedure?</h2>
<A NAME="TI1449"></A><p>Oracle defines a <strong>stored procedure</strong> (or
function) as a named PL/<ACRONYM title = "sequel" >SQL</ACRONYM> program unit
that logically groups a set of <ACRONYM title = "sequel" >SQL</ACRONYM> and
other PL/<ACRONYM title = "sequel" >SQL</ACRONYM> programming language
statements together to perform a specific task.</p>
<A NAME="TI1450"></A><p>Stored procedures can take parameters and return one or more
result sets (also called cursor variables). You create stored procedures
in your schema and store them in the data dictionary for use by
multiple users.</p>
<A NAME="CCJBDEFC"></A><h2>What you can do with Oracle stored procedures</h2>
<A NAME="TI1451"></A><h4>Ways to use Oracle stored procedures</h4>
<A NAME="TI1452"></A><p>You can use an Oracle stored procedure in the following ways
in your PowerBuilder application:<A NAME="TI1453"></A>
<ul>
<li class=fi>As a data source for DataWindow objects</li>
<li class=ds>Called by an embedded <ACRONYM title = "sequel" >SQL</ACRONYM> <b>DECLARE
PROCEDURE</b> statement in a PowerBuilder application (includes
support for fetching against stored procedures with result sets)</li>
<li class=ds>Called as an external function or subroutine in
a PowerBuilder application by using the <b>RPCFUNC</b> keyword
when you declare the procedure
</li>
</ul>
</p>
<A NAME="TI1454"></A><p>For information about the syntax for using
the <b>DECLARE PROCEDURE</b> statement with the <b>RPCFUNC</b> keyword,
see the <i>PowerScript Reference</i>
.</p>
<p><b>Procedures with a single result set</b>   You can use stored procedures that return a single result
set in DataWindow objects and embedded <ACRONYM title = "sequel" >SQL</ACRONYM>,
but <i>not</i> when using the <b>RPCFUNC</b> keyword
to declare the stored procedure as an external function or subroutine.</p>
<p><b>Procedures with multiple result sets</b>   You can use procedures that return multiple result sets <i>only</i> in
embedded <ACRONYM title = "sequel" >SQL</ACRONYM>. Multiple result
sets are <i>not supported</i> in DataWindows, reports,
or with the <b>RPCFUNC</b> keyword. </p>
<A NAME="TI1455"></A><h2>Using Oracle stored procedures with result sets</h2>
<A NAME="TI1456"></A><h4>Overview of basic steps</h4>
<A NAME="TI1457"></A><p>The following procedure assumes you are creating the stored
procedure in the <ACRONYM title = "I sequel" >ISQL</ACRONYM> view
of the Database painter in PowerBuilder.</p>
<A NAME="TI1458"></A><p><img src="images/proc.gif" width=17 height=17 border=0 align="bottom" alt="Steps"> To use an Oracle stored procedure with a result
set:</p>
<ol><li class=fi><p>Set up the <ACRONYM title = "I sequel" >ISQL</ACRONYM> view
of the Database painter to create the stored procedure.</p></li>
<li class=ds><p>Create the stored procedure with a result set
as an <b>IN OUT</b> (reference) parameter.</p></li>
<li class=ds><p>Create DataWindow objects that use the stored procedure
as a data source.</p></li></ol>
<br><A NAME="TI1459"></A><h4>Setting up the Database painter</h4>
<A NAME="TI1460"></A><p>When you create a stored procedure in the <ACRONYM title = "I sequel" >ISQL </ACRONYM>view of the Database painter, you
must change the default <ACRONYM title = "sequel" >SQL</ACRONYM> statement
terminator character to one that you do not plan to use in your
stored procedure syntax.</p>
<A NAME="TI1461"></A><p>The default <ACRONYM title = "sequel" >SQL</ACRONYM> terminator
character for the Database painter is a semicolon (;). If you plan
to use a semicolon in your Oracle stored procedure syntax, you must
change the painter's terminator character to something
other than a semicolon to avoid conflicts. A good choice is the
backquote ( ` ) character.</p>
<A NAME="TI1462"></A><p><img src="images/proc.gif" width=17 height=17 border=0 align="bottom" alt="Steps"> To change the default <ACRONYM title = "sequel" >SQL</ACRONYM> terminator
character in the Database painter:</p>
<ol><li class=fi><p>Connect to your Oracle database in PowerBuilder as
the System user.</p><p>For instructions, see <A HREF="connpbp68.htm#CCJDAAGF">"Defining the Oracle database
interface"</A>.</p></li>
<li class=ds><p>Open the Database painter.</p></li>
<li class=ds><p>Select Design&gt;Options from the menu bar.</p><p>The Database Preferences dialog box displays. If necessary,
click the General tab to display the General property page.</p></li>
<li class=ds><p>Type the character you want (for example, a backquote)
in the <ACRONYM title = "sequel" >SQL</ACRONYM> Terminator Character
box.</p></li>
<li class=ds><p>Click Apply or OK.</p><p>The <ACRONYM title = "sequel" >SQL</ACRONYM> Terminator Character
setting is applied to the current connection and all future connections
(until you change it).</p></li></ol>
<br><A NAME="TI1463"></A><h4>Creating the stored procedure</h4>
<A NAME="TI1464"></A><p>After setting up the Database painter, you can create an Oracle
stored procedure that has a result set as an <b>IN OUT </b>(reference)
parameter. PowerBuilder retrieves the result set to populate a DataWindow object.</p>
<A NAME="TI1465"></A><p>There are many ways to create stored procedures with result
sets. The following procedure describes one possible method that
you can use. </p>
<A NAME="TI1466"></A><p>For information about when you can use stored
procedures with single and multiple result sets, see <A HREF="connpbp69.htm#CCJBDEFC">"What you can do with Oracle
stored procedures"</A>.</p>
<A NAME="TI1467"></A><p><img src="images/proc.gif" width=17 height=17 border=0 align="bottom" alt="Steps"> To create Oracle stored procedures with result
sets:</p>
<ol><li class=fi><p>Make sure your Oracle user account has
the necessary database access and privileges to access Oracle objects
(such as tables and procedures).</p><p>Without the appropriate access and privileges, you will be
unable to create Oracle stored procedures.</p></li>
<li class=ds><p>Assume the following table named <b>tt</b> exists
in your Oracle database:</p><A NAME="TI1468"></A><table cellspacing=0 cellpadding=6 border=1 frame="void" rules="all"><tr><th  rowspan="1"  ><A NAME="TI1469"></A>a</th>
<th  rowspan="1"  ><A NAME="TI1470"></A>b</th>
<th  rowspan="1"  ><A NAME="TI1471"></A>c</th>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1472"></A>1</td>
<td  rowspan="1"  ><A NAME="TI1473"></A>Newman</td>
<td  rowspan="1"  ><A NAME="TI1474"></A>sysdate</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1475"></A>2</td>
<td  rowspan="1"  ><A NAME="TI1476"></A>Everett</td>
<td  rowspan="1"  ><A NAME="TI1477"></A>sysdate</td>
</tr>
</table>
</li>
<li class=ds><p>Create an Oracle package that holds the result
set type and stored procedure. The result type must match your table
definition.</p><p>For example, the following statement creates an Oracle package
named <b>spm</b> that holds a result set type named <b>rctl</b> and
a stored procedure named <b>proc1</b>. The tt%ROWTYPE
attribute defines <b>rctl</b> to contain all of the columns
in table <b>tt</b>. The procedure <b>proc1</b> takes
one parameter, a cursor variable named <b>rc1</b> that
is an <b>IN OUT</b> parameter of type <b>rctl</b>.<p><PRE> CREATE OR REPLACE PACKAGE spm<br>   IS TYPE rctl IS REF CURSOR<br>   RETURN tt%ROWTYPE;<br>   PROCEDURE proc1(rc1 IN OUT rctl);END;`</PRE></p></li>
<li class=ds><p>Create the Oracle stored procedure separately
from the package you defined.</p><p>The following examples show how to create two stored procedures: <i>spm_proc
1</i> (returns a single result set) and <b>spm_proc2</b> (returns
multiple result sets).</p><p>The <b>IN OUT</b> specification means that PowerBuilder passes
the cursor variable (<b>rc1</b> or <b>rc2</b>)
by reference to the Oracle procedure and expects the procedure to
open the cursor. After the procedure call, PowerBuilder fetches the
result set from the cursor and then closes the cursor.</p></li>
<li class=ds><b>spm_proc1 example for DataWindow objects</b>   The following statements create <b>spm_proc1 </b>which
returns one result set. You can use this procedure as the data source
for a DataWindow object in PowerBuilder.</p>
<p><p><PRE> CREATE OR REPLACE PROCEDURE spm_proc1(rc1 IN OUT      spm.rctl)<br>AS<br>BEGIN<br>   OPEN rc1 FOR SELECT * FROM tt;<br>END;`</PRE></p></li>
<li class=ds><b>spm_proc2 example for embedded <ACRONYM title = "sequel" >SQL</ACRONYM></b>   The following statements create <b>spm_proc2</b> which
returns two result sets. You can use this procedure only in embedded <ACRONYM title = "sequel" >SQL</ACRONYM>.</p>
<p><p><PRE> CREATE OR REPLACE PROCEDURE spm_proc2 (rc1 IN OUT spm.rctl, rc2 IN OUT spm.rctl)<br>AS<br>BEGIN<br>   OPEN rc1 FOR SELECT * FROM tt ORDER BY 1;<br>   OPEN rc2 FOR SELECT * FROM tt ORDER BY 2;END;`</PRE></p></li></ol>
<br><br><img src="images/note.gif" width=17 height=17 border=0 align="bottom" alt="Note"> <span class=shaded>Error checking</span> <A NAME="TI1478"></A>If necessary, check the Oracle system table <b>public.user_errors</b> for
a list of errors.
<br>
<A NAME="TI1479"></A><h4>Creating the DataWindow object</h4>
<A NAME="TI1480"></A><p>After you create the stored procedure, you can define the DataWindow object that
uses the stored procedure as a data source.</p>
<A NAME="TI1481"></A><p>You can use Oracle stored procedures that return a single
result set in a DataWindow object. If your stored procedure returns multiple
result sets, you must use embedded <ACRONYM title = "sequel" >SQL</ACRONYM> commands
to access it.</p>
<A NAME="TI1482"></A><p>The following procedure assumes that your Oracle stored procedure
returns only a single result set.</p>
<A NAME="TI1483"></A><p><img src="images/proc.gif" width=17 height=17 border=0 align="bottom" alt="Steps"> To create a DataWindow object using an Oracle stored
procedure with a result set:</p>
<ol><li class=fi><p>Select a presentation style on the DataWindow
page of the New dialog box and click OK.</p></li>
<li class=ds><p>Select the Stored Procedure icon and click OK.</p><p>The Select Stored Procedure wizard page displays, listing
the stored procedures available in your database.</p></li>
<li class=ds><p>Select the stored procedure you want to use as
a data source, and click Next.</p></li>
<li class=ds><p>Complete the wizard to define the DataWindow object.</p><p>When you preview the DataWindow object or call <b>Retrieve</b>, PowerBuilder fetches
the result set from the cursor in order to populate the DataWindow object.
If you selected Retrieve on Preview on the Choose Data Source page
in the wizard, the result set displays in the Preview view when
the DataWindow opens.</p></li></ol>
<br><A NAME="TI1484"></A><h2>Using a large-object output parameter</h2>
<A NAME="TI1485"></A><p>You can define a large object (LOB) as an output parameter
for an Oracle stored procedure or function to retrieve large-object
data. There is no limit on the number of LOB output arguments that
can be defined for each stored procedure or function.</p>
<A NAME="TI1486"></A><p>In Oracle 10<i>g</i>, the maximum size of LOB
datatypes has been increased from 4 gigabytes minus 1 to 4 gigabytes
minus 1 multiplied by the block size of the database. For a database
with a block size of 32K, the maximum size is 128 terabytes.</p>

